﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DynamicToSql
{
    public static class StatementHelper
    {
        public static TValue ToScalar<TValue>(IStatement statement)
        {
            IDbCommand command = null;

            try
            {
                command = CreateCommand(statement);

                var value = (TValue)command.ExecuteScalar();

                UpdateOuputAndReturnValues(statement, command);

                return value;

            }
            finally
            {
                command.Dispose();
                command = null;
            }
        }

        public static IList<dynamic> ToList(IStatement statement)
        {
            IDbCommand command = null;
            IDataReader reader = null;
            bool readerDisposed = false;

            try
            {
                command = CreateCommand(statement);

                reader = command.ExecuteReader();

                var list = ToList(statement.Session.NamingConverter, reader);

                reader.Dispose();
                reader = null;
                readerDisposed = true;

                UpdateOuputAndReturnValues(statement, command);

                return list;
            }
            finally
            {
                if (!readerDisposed)
                {
                    reader.Dispose();
                    reader = null;
                }

                command.Dispose();
                command = null;
            }
        }

        public static IList<IList<dynamic>> ToMultipleList(IStatement statement)
        {
            IDbCommand command = null;
            IDataReader reader = null;
            bool readerDisposed = false;

            try
            {
                command = CreateCommand(statement);

                reader = command.ExecuteReader();

                var list = ToMultipleList(statement.Session.NamingConverter, reader);

                reader.Dispose();
                reader = null;
                readerDisposed = true;

                UpdateOuputAndReturnValues(statement, command);

                return list;
            }
            finally
            {
                if (!readerDisposed)
                {
                    reader.Dispose();
                    reader = null;
                }

                command.Dispose();
                command = null;
            }
        }

        public static int Execute(IStatement statement)
        {
            IDbCommand command = null;

            try
            {
                command = CreateCommand(statement);

                var rows = command.ExecuteNonQuery();

                UpdateOuputAndReturnValues(statement, command);

                return rows;
            }
            finally
            {
                command.Dispose();
                command = null;
            }
        }

        private static IDbCommand CreateCommand(IStatement statement)
        {
            if (statement.Session.Logger != null)
            {
                statement.Session.Logger.Log(statement);
            }

            var command = statement.Session.Connection.CreateCommand();
            command.CommandText = statement.Sql;
            command.CommandType = statement.Type == StatementType.Sql ? CommandType.Text : CommandType.StoredProcedure;
            command.Transaction = statement.Session.Transaction != null && statement.Session.Transaction.State == TransactionState.New
                                  ? statement.Session.Transaction.InnerTransaction : null;

            foreach (var param in statement.GetParameters())
            {
                var paramName = param.Key;

                var parameter = param.Value;

                IDbDataParameter dp = command.CreateParameter();

                dp.ParameterName = paramName;

                dp.Value = parameter.Value ?? DBNull.Value;

                if (parameter.Type.HasValue)
                    dp.DbType = parameter.Type.Value;

                if (parameter.Direction.HasValue)
                    dp.Direction = parameter.Direction.Value;

                command.Parameters.Add(dp);
            }

            return command;
        }

        private static void UpdateOuputAndReturnValues(IStatement statement, IDbCommand command)
        {
            foreach (var param in statement.GetParameters().ToArray())
            {
                var paramName = param.Key;

                var parameter = param.Value;

                if (parameter.Direction.HasValue)
                {
                    var dp = (IDbDataParameter)command.Parameters[paramName];

                    if (parameter.Direction == ParameterDirection.InputOutput
                        || parameter.Direction == ParameterDirection.Output
                        || parameter.Direction == ParameterDirection.ReturnValue)
                        statement.Parameter(paramName, dp.Value, parameter.Type.Value, parameter.Direction.Value);
                }
            }
        }

        private static IList<dynamic> ToList(INamingConverter namingConverter, IDataReader reader)
        {
            var list = new List<dynamic>();

            while (reader.Read())
            {
                Entity item = new Entity();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    var column = reader.GetName(i);
                    item[namingConverter.ToProperty(column)] = reader[column] != DBNull.Value ? reader[column] : null;
                }

                list.Add((dynamic)item);
            }

            return list;
        }

        private static IList<IList<dynamic>> ToMultipleList(INamingConverter namingConverter, IDataReader reader)
        {
            var list = new List<IList<dynamic>>();

            do
            {
                var l = new List<dynamic>();

                while (reader.Read())
                {
                    Entity item = new Entity();

                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        var column = reader.GetName(i);
                        item[namingConverter.ToProperty(column)] = reader[column] != DBNull.Value ? reader[column] : null;
                    }

                    l.Add((dynamic)item);
                }

                list.Add(l);

            } while (reader.NextResult());

            return list;
        }

        public static void AddParameters(IStatement statement, Entity instance, PropertySelector selector)
        {
            foreach (var p in instance.GetProperties())
            {
                if (selector == null)
                {
                    statement.Parameter(p.Key, p.Value);
                }
                else if (selector.IncludeProperties.Count > 0)
                {
                    if (selector.IncludeProperties.Contains(p.Key))
                    {
                        statement.Parameter(p.Key, p.Value);
                    }
                }
                else if (selector.ExcludeProperties.Count > 0)
                {
                    if (!selector.ExcludeProperties.Contains(p.Key))
                    {
                        statement.Parameter(p.Key, p.Value);
                    }
                }
                
            }
        }

        public static void AddParameters(IStatement statement, Criteria criteria)
        {
            int paramIndex = 0;
            AddParameters(statement, criteria, ref paramIndex);
        }

        private static void AddParameters(IStatement statement, Criteria criteria, ref int paramIndex)
        {
            foreach (object criteron in criteria.Criterions)
            {
                if (criteron is Expression)
                {
                    var exp = (Expression)criteron;

                    var property = exp.Property;

                    if (exp.ComparisonOperator == ComparisonOperator.Equal)
                    {
                        if (exp.Values[0] != null)
                        {
                            var paramName = "p" + paramIndex++;
                            statement.Parameter(paramName, exp.Values[0]);
                        }
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.NotEqual)
                    {
                        if (exp.Values[0] != null)
                        {
                            var paramName = "p" + paramIndex++;
                            statement.Parameter(paramName, exp.Values[0]);
                        }
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.GreaterThan ||
                             exp.ComparisonOperator == ComparisonOperator.GreaterThanOrEqual ||
                             exp.ComparisonOperator == ComparisonOperator.LessThan ||
                             exp.ComparisonOperator == ComparisonOperator.LessThanOrEqual ||
                             exp.ComparisonOperator == ComparisonOperator.Like)
                    {
                        var paramName = "p" + paramIndex++;
                        statement.Parameter(paramName, exp.Values[0]);
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.In)
                    {
                        foreach (var v in exp.Values) 
                        {
                            var paramName = "p" + paramIndex++;
                            statement.Parameter(paramName, v);
                        }
                    }
                    else if (exp.ComparisonOperator == ComparisonOperator.Between)
                    {
                        var paramName = "p" + paramIndex++;
                        statement.Parameter(paramName, exp.Values[0]);
                        var paramName2 = "p" + paramIndex++;
                        statement.Parameter(paramName2, exp.Values[1]);
                    }

                }
                else if (criteron is Criteria)
                {
                    var c = (Criteria)criteron;
                    AddParameters(statement, c, ref paramIndex);
                }
            }
        }

    }
}
